Diagram listing configuration

In the Project Environment dialog, in [library or project] > Diagram > Diagram Listing Configuration, the administrator can define configurations that allow diagram designers to generate diagram object listings in Microsoft Excel format. P&ID can output listings in three different ways, as specified in the configuration: all objects in the same sheet, one object in one sheet (multiple sheets), or one object in one workbook file.

Creating a diagram listing template

Create a Microsoft Excel template that you can use in diagram listing configurations. The template file can contain header cells and data cells that you then map in the listing configuration, so that the cells can be automatically populated when a diagram designer generates a listing.

Do the following:

  1. Use Microsoft Excel to create the template file.

    1. Create a new spreadsheet file.

    2. Decide how many sheets to have in the template file. One sheet is enough if the generated files are to contain only data sheets, and two sheets are required if the generated files should also contain a summary sheet.

    3. Design the template. A listing template can contain:

      • Static information such as your company name or logo.

      • Header cells.

      • One or multiple rows/columns of data.

      • Any special formatting: column filters, cell formatting (number, date…), fonts, background colors, cell borders, and so on.

    4. Save the template in the file system.

  2. Use the CADMATIC desktop to save the template in COS.

    1. Select Object > Library and Project Databases. The Project Environment dialog opens.

    2. Browse to [library or project] > Document Production > Excel Template.

    3. Select New > Excel Template, select the Excel file from the file system, and click Open. The New Excel Template dialog opens.

    4. Enter a name and description for the template, and click OK. The new template is shown in the template list.

Creating a diagram listing configuration

Create a diagram listing configuration that defines what data to include in header cells and data cells when a diagram designer generates a listing. Header cells can take their value from a PI_CATALOG column, a diagram COS object attribute, or a project COS object attribute. Data cells can take their value from, for example, a data request, an SQL database column, or an attribute.

Prerequisites

Do the following:

  1. Use the P&ID application to create a new listing configuration.

    1. Select File > Environment > All Library and Project.

    2. Browse to [library or project] > Diagram > Diagram Listing Configuration.

    3. Select New > Diagram Listing Configuration. The Edit Object Attributes dialog opens.

    4. Enter a name and description for the configuration, and click OK. The Listing Configuration dialog opens.

  2. Define the general properties.

    • Listing type – Select what kind of listings to create with this configuration.

      • Listing – Creates one Excel file where all the objects are listed in the same sheet, and you can have the data sorted by one or more columns. The default file name is the name of the Excel template.

      • Separate data sheets – Creates a separate Excel file for each object, and the default file name is the name of the object.

      • All sheets in one file – Creates one Excel file where each object is in a separate sheet. The file name is always the Data table name defined in the Data Table Editor dialog, and the default sheet name is the name of the object.

    • Output filename – You can define a custom output name that can consist of normal text, attribute tags (defined as {attribute:<tag>}), and main table and sub-table names (defined as {column:<maintable>.<subtable>}). Examples:

      • {attribute:.dU}

      • Valve {column:ARMATURES.PosId}

      • {column:PI_CATALOG.Name}_Rev-{column:PI_CATALOG.Revision}

      Note: In listings of type "Listing" and "Separate data sheets" this affects the name of the Excel file, and in listings of type "All sheets in one file" this affects the name of the Excel sheet. For data sheet outputs, define a naming pattern that generates object-specific names.

    • Excel Template – Select the Excel template from COS.

    • Use summary sheet – If the Excel template contains more than one sheet, this option is displayed. Selecting this option shows an additional option Running sheet and an additional Summary data pane in the dialog.

  3. In the Header data section, define the data to be inserted into the header cells of the Excel file.

    • New – Opens the Excel Header Cell Configuration dialog for creating a new header cell mapping.

      • Excel cell – Select the cell where the mapped data is to be inserted.

        1. Click Pick. The Excel template (and a dialog for closing the template—do not click OK yet!) opens.

        2. Select the cell where you want the data to be inserted.

        3. Click OK in the separate dialog. The Excel template is closed.

      • Mapped to – Map the cell you selected from the template to a data source.

        1. Click Map. The Select column dialog opens.

        2. Select a column from the PI_CATALOG table, a diagram COS object attribute, or a project COS object attribute, and click OK.

      • Description – Enter a description for the mapping, and click OK.

        Note: This description is only shown in the Listing Configuration dialog. It is not inserted into the Excel file.

    • Edit – Opens the selected header cell mapping for editing.

    • Delete – Deletes the selected header cell mapping.

  4. In the Data tables section, define the data cells to be inserted into the Excel file.

    • Running sheet – If you chose to include a summary sheet in the generated files, this field lists the sheets of the template file. Select which sheet to use as the template for the data sheets.

    • New – Opens the Data Table Editor dialog for creating a new data request.

    • Edit – Opens the selected data request for editing.

    • Delete – Deletes the selected data request.

  5. In the Summary data section (if enabled), define the data cells to be inserted into the summary sheet of the Excel file.

    • New – Opens the Data Table Editor dialog for creating a new data request.

    • Edit – Opens the selected data request for editing.

    • Delete – Deletes the selected data request.

  6. The Descriptions in Excel setting allows the Description texts defined in Data Table Editor to be inserted into the data table (does not affect header cells).

    • Cell to the left from value – Insert the descriptions to the left side of the data.

    • Cell above value – Insert the descriptions above the data.

    • No descriptions – Do not insert descriptions to listings.

    Note: If the Excel template already has something in the given cell, then the inserted Description value overwrites the template value. If the Description field is left empty, then the template value is preserved.

  7. Click OK. The new configuration is shown in the configuration list.

Data Table Editor

You can define which objects and what data to include in diagram listings. If the listing is of type "Listing", you can also have the data automatically sorted in the output by one or more columns, in ascending order of the values.

Do the following:

  1. In the Listing Configuration dialog, in the Data tables section, click New. The Data Table Editor dialog opens.

  2. Define the general properties.

    • Data table name – Enter a name for the data table. This name is displayed in the Listing Configuration dialog, and if the listing is of type "All sheets in one file" it is also used as the name of the Excel file.

    • Main table – Select the required main table: ARMATURES, EQUIPMENTS, INSTRUMENTS, or ELECTRICS.

    • Object selection query – Click Edit to open a dialog where you can define which sub-tables to include and create rules for selecting objects from those sub-tables: Selection Query Editor.

  3. In the Data requests section, define the data to be inserted into the data table.

    • New – Opens the Excel Header Cell Configuration dialog for defining a new data mapping.

      • Excel cell – Select the cell where the mapped data is to be inserted.

        1. Click Pick. The Excel template (and a dialog for closing the template—do not click OK yet!) opens.

        2. Select the cell where you want the data to be inserted.

        3. Click OK in the separate dialog. The Excel template is closed.

      • Type – Select what data to show in the data cell.

        • Data request – Use a data request to retrieve a value from the SQL or the COS database, and possibly process the value in some ways before inserting it into the Excel file. Closing the mapping dialog opens the Combined Data Requests for Label Definition dialog where you can define the data request. The data request can change the format of the value, add a prefix or suffix to the value, and even combine several columns to one text output. See Diagram label definitions.

        • Attribute – Shows the value of an attribute in the data cell. Closing the mapping dialog opens the Select column dialog where you can select the required attribute.

        • Column – Shows the value of a P&ID database column in the data cell. Closing the mapping dialog opens the Select column dialog where you can select the required column.

        • List of diagrams – Shows a semicolon-separated list of diagrams that contain the given object.

        • Linked to 3D model – Shows whether the given object is linked to the 3D model via an EPD or EDM integration object.

        Description – Enter a description for the mapped data.

        Note: You can use the Descriptions in Excel function in the Listing Configuration dialog to insert this description into the Excel file. You can use this, for example, to overwrite some of the title cells defined in the Excel template.

    • Edit – Opens the selected data mapping for editing.

    • Edit Request – If the data mapping uses a data request, opens the data request for editing.

    • Delete – Deletes the selected data mapping.

      If the listing is of type "Listing", you can select to sort the data table by one or more columns:

    • Use in sorting – Select this option to sort the data table by the values of the selected data mapping.

    • Up, Down – Define which columns are sorted before others. Sorting is applied to the data mappings from top to bottom—move the one that should be sorted first to the top of the list, and arrange the other ones in the desired sorting order.

  4. When you have defined the required data mappings, click OK to close the editor.

Selection Query Editor

You can specify which sub-tables to include when generating a diagram listing and define rule sets that an object must fulfill to be selected from those sub-tables. For example, only the VALVES sub-table, and from there only those valves that have a Position ID.

Do the following:

  1. In the Data Table Editor dialog, in the Object selection query setting, click Edit. The Selection Query Editor dialog opens.

  2. In the Subtables section, select which sub-tables to include when getting data for listings.

  3. In the Rule sets section, define rule sets that select objects from the included sub-tables.

    • New – Opens the Selection Query Rule Set Editor dialog for creating a new rule set.

    • Edit – Opens the selected rule set for editing.

    • Delete – Deletes the selected rule set.

  4. When you have defined the required rule sets, click OK to close the editor. The data request is displayed in the Data Table Editor dialog.

Selection Query Rule Set Editor

You can define a set of rules that an object must fulfill to be selected from a sub-table.

Do the following:

  1. In the Selection Query Editor dialog, click New. The Selection Query Rule Set Editor dialog opens.

  2. In the Rules section, define rules that select objects from a given sub-table.

    • New – Opens the Selection Query Rule Editor dialog for creating a new rule.

    • Edit – Opens the selected rule for editing.

    • Delete – Deletes the selected rule.

  3. When you have defined the required rules, click OK to close the editor. The rule set is displayed in the Selection Query Editor dialog.

Selection Query Rule Editor

You can define a rule that an object must fulfill to be selected from a sub-table.

Do the following:

  1. In the Selection Query Rule Set Editor dialog, click New. The Selection Query Rule Editor dialog opens.

  2. Define a rule for selecting objects from a given sub-table.

    • Table – Select the sub-table from the list.

    • Column – Select the data column from the list.

    • Operator – Select the query operator from the list and specify the expected value (if applicable).

  3. Click OK. The rule is displayed in the Selection Query Rule Set Editor dialog.

Example

The template has a header cell for the project name and a number of columns for the object data:

In the data mappings, the NS values are set to be sorted, and the values are retrieved using a data request that formats the value by removing digits and adding the unit:

A listing of type "Listing", arranged by NS values:

A listing of type "Separate data sheets":

A listing of type "All sheets in one file" with column headings (descriptions) defined in the data mappings:

Related Topics

Listing